package com.termine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import com.termine.db.Connector;
import com.termine.entity.PersonalData;

@ManagedBean
@ApplicationScoped
public class PersonalDataDAO {

	public void addProvider(PersonalData personalData) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "insert into PROVIDER (PD_ID) values (?)";

		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getPersonal_data_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public Integer getProviderId(PersonalData personalData) throws Exception {
		Integer result = null;
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select P_ID from PROVIDER where PD_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getPersonal_data_id());
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				result = resultSet.getInt("P_ID");
				if (resultSet.wasNull()) {
					result = null;
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public void addClient(PersonalData personalData) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "insert into CLIENT (PD_ID) values (?)";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getPersonal_data_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public Integer getClientId(PersonalData personalData) throws Exception {
		Integer result = null;
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select C_ID from CLIENT where PD_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getPersonal_data_id());
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				result = resultSet.getInt("C_ID");
				if (resultSet.wasNull()) {
					result = null;
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public void addPersonalData(PersonalData personalData) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "insert into PERSONAL_DATA (L_ID, PD_FORENAME, "
				+ "PD_SIRNAME, PD_PHONE_NUMBER, PD_EMAIL, PD_PASSWORD) "
				+ "values (?,?,?,?,?,?)";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getLocation_id());
			statement.setString(2, personalData.getForename());
			statement.setString(3, personalData.getSirname());
			statement.setString(4, personalData.getPhone_number());
			statement.setString(5, personalData.getEmail());
			statement.setString(6, personalData.getPassword());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public Integer getPersonalDataId(PersonalData personalData)
			throws Exception {
		Integer result = null;
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select PD_ID from PERSONAL_DATA where L_ID = ? "
				+ "and PD_FORENAME = ? and PD_SIRNAME = ? and "
				+ "PD_PHONE_NUMBER = ? and PD_EMAIL = ? and PD_PASSWORD = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getLocation_id());
			statement.setString(2, personalData.getForename());
			statement.setString(3, personalData.getSirname());
			statement.setString(4, personalData.getPhone_number());
			statement.setString(5, personalData.getEmail());
			statement.setString(6, personalData.getPassword());
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				result = new Integer(resultSet.getInt("PD_ID"));
				if (resultSet.wasNull()) {
					result = null;
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public PersonalData getPersonalData(String email, String password) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select * from PERSONAL_DATA where PD_EMAIL = ? and PD_PASSWORD = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setString(1, email);
			statement.setString(2, password);
			ResultSet resultSet = statement.executeQuery();

			PersonalData result = null;

			resultSet.next();
			if (!resultSet.isClosed()) {
				result = new PersonalData();
				String string;
				Integer integer;

				integer = new Integer(resultSet.getInt("PD_ID"));
				if (!resultSet.wasNull()) {
					result.setPersonal_data_id(integer);
				}
				integer = new Integer(resultSet.getInt("L_ID"));
				if (!resultSet.wasNull()) {
					result.setLocation_id(integer);
				}
				integer = new Integer(resultSet.getInt("C_ID"));
				if (!resultSet.wasNull()) {
					result.setClient_id(integer);
				}
				integer = new Integer(resultSet.getInt("P_ID"));
				if (!resultSet.wasNull()) {
					result.setProvider_id(integer);
				}
				string = resultSet.getString("PD_FORENAME");
				if (!resultSet.wasNull()) {
					result.setForename(string);
				}
				string = resultSet.getString("PD_SIRNAME");
				if (!resultSet.wasNull()) {
					result.setSirname(string);
				}
				string = resultSet.getString("PD_PHONE_NUMBER");
				if (!resultSet.wasNull()) {
					result.setPhone_number(string);
				}
				string = resultSet.getString("PD_EMAIL");
				if (!resultSet.wasNull()) {
					result.setEmail(string);
				}
				string = resultSet.getString("PD_PASSWORD");
				if (!resultSet.wasNull()) {
					result.setPassword(string);
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public void updatePersonalData(PersonalData personalData) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "update PERSONAL_DATA set L_ID = ?, C_ID = ?, "
				+ "P_ID = ?, PD_FORENAME = ?, PD_SIRNAME = ?, "
				+ "PD_PHONE_NUMBER = ?, PD_EMAIL = ?, PD_PASSWORD = ? "
				+ "where PD_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			if (personalData.getLocation_id() != null) {
				statement.setInt(1, personalData.getLocation_id());
			} else {
				statement.setNull(1, java.sql.Types.INTEGER);
			}
			if (personalData.getClient_id() != null) {
				statement.setInt(2, personalData.getClient_id());
			} else {
				statement.setNull(2, java.sql.Types.INTEGER);
			}
			if (personalData.getProvider_id() != null) {
				statement.setInt(3, personalData.getProvider_id());
			} else {
				statement.setNull(3, java.sql.Types.INTEGER);
			}
			statement.setString(4, personalData.getForename());
			statement.setString(5, personalData.getSirname());
			statement.setString(6, personalData.getPhone_number());
			statement.setString(7, personalData.getEmail());
			statement.setString(8, personalData.getPassword());
			statement.setInt(9, personalData.getPersonal_data_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

}
